package servlet.user;

import entity.NewsType;
import entity.User;
import util.*;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

import static util.BaseDao.*;

public class SelectServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        List<User> list = new ArrayList();
        Number total = 0;
        try {
            // 获取参数
            User params = (User) GetParamToClass.setObjectFiled(req, User.class);
            Map<String, Object> pIndex = new LinkedHashMap<>(); //sql参数
            String page1 = req.getParameter("page");
            Integer page = 0;
            Integer pageSize = 10;
            if (!Empty.isEmpty(page1)) {
                page = Integer.valueOf(page1)-1;
            }
            if (!Empty.isEmpty(req.getParameter("pageSize"))) {
                pageSize = Integer.valueOf(req.getParameter("pageSize"));
            }
            String order = req.getParameter("order"); //DESC ASC
            String keyword = req.getParameter("keyword"); //搜索关键字
            if ("".equals(order) || null == order) {
                order = "DESC";
            }
            connection = DButils.getConnection();
            String tableName = getTableName(User.class);
            List<String> fields= getFields(User.class);
            StringBuilder sb = new StringBuilder("SELECT  ");
            sb.append(String.join(",", fields));
            sb.append(" FROM " + tableName);
            sb.append(" WHERE 1=1 ");
            // 查询总数sql
            StringBuilder totalSql = new StringBuilder("select count(id) as total FROM " + tableName);
            //模糊查询sql
            // 关键字查询参数设置，由于set集合key不能重复，关键字字段就用 keyword加索引
            if (!Empty.isEmpty(keyword)) {
                for (int i = 0; i < 2; i++) {
                    pIndex.put("keyword" + i, "%"+keyword+"%");
                }
                totalSql.append(" and ( rel_name like ? OR phone like ? )");
                sb.append(" and ( rel_name like ? OR phone like ? )");
            }
            sb.append(" ORDER BY reg_time " + order + " LIMIT ?,?");
            pIndex.put("page", page);
            pIndex.put("pageSize", pageSize);
            String sql = sb.toString();
            preparedStatement = connection.prepareStatement(sql);
            Set<String> strings = pIndex.keySet(); // sql参数 keyset
            Iterator<String> iterator = strings.iterator(); // keyset 迭代器，遍历参数名和值，
            //获取map keyset 设置sql参数
            for (int j = 0; j < strings.size(); j++) {
                Object o = "";
                if (iterator.hasNext()) {
                    o = iterator.next();
                }
                preparedStatement.setObject(j+1 , pIndex.get(o));
            }
            //执行查询

            ResultSet resultSet = preparedStatement.executeQuery(); // 查询结果
            //获取PrepareStatement里面编译好的SQL语句
            System.out.println("sql ===> "+getSql(preparedStatement));
            while (resultSet.next()) { // 判断是否有数据
                User user = new User();
                // 反射给实体赋值
                user = (User) setField(fields,User.class,resultSet);
                // 把查询到的一行加入list
                list.add(user);
            }
            // 获取总条数
            preparedStatement = connection.prepareStatement(totalSql.toString());
            //获取map keyset 设置sql参数 没有page 和 pageSize 所以要比查询少两个参数
            for (int j = 0; j < strings.size()-2; j++) {
                Object o = "";
                if (iterator.hasNext()) {
                    o = iterator.next();
                }
                preparedStatement.setObject(j+1 , pIndex.get(o));
            }
            ResultSet totalSet = preparedStatement.executeQuery(); // 查询结果
            System.out.println("sql ===> "+getSql(preparedStatement));
            if (totalSet.next()) {
                total = (Number)totalSet.getObject("total");
            }
            ResposeUtil.write(R.data(R.CODE_SUCCESS,list,total), resp);
        } catch (Exception e) {
            e.printStackTrace();
            ResposeUtil.write(R.data(R.CODE_SYS_ERR, R.MSG_SYS_ERR), resp);
        } finally {
            DButils.close(connection, preparedStatement);
        }
    }
}
